<?php

class Groups_model extends Onecore_Model
{

    public function __construct()
    {
        parent::__construct();
        $this->_name = "groups";
        $this->_id = array("id");
        $this->_fields = array("id","name","description","deleted");
        $this->_fieldsConditions = array("ModuloId"=>"where","name"=>"like","description"=>"like");
        $this->_itemName = "tbl_modulos";
        $this->_validations = array(array("field"=>"name","label"=>"name","rules"=>"required"));
        
    }
    public function loadListCrud($options){
        $items = array();
        $condition = "";
        $count = 0;
        foreach($options->conditions as $key=>$value){
            if(!empty($value)){
                if($key == "o.name" || $key == "o.description" || $key == "o.ucreated" || $key == "o.umodified")
                    $condition .= "and $key like '%$value%' "; 
                else{
                    if($key != "from" && $key != "to"){
                        $condition .= "and $key = '$value' "; 
                    }
                }
            }  
        }

        if(!empty($options->conditions->from)){
            $from = $options->conditions->from;
            $condition .= "and o.dcreated >= '$from' ";
        }
            
        if(!empty($options->conditions->to)){
            $to = $options->conditions->to;
            $condition .= "and o.dcreated <= '$to' ";
        }

        //si quiere ver los eliminiados
        if($options->conditions->deleted != ""){
            $deleted = $options->conditions->deleted;
            $condition .= "and o.deleted = '$deleted' ";
        }        

        //obtenemos total de rtegistros sin filtros
        $count=0;
        $sqlCount = "select count(*) as count 
                    from $this->_name o, typegropus c 
                    where o.typegroups_id = c.id
                    $condition";

        //echo $sqlCount;exit;


        $queryCount = $this->db->query($sqlCount);        
        if ($queryCount->num_rows() > 0){
            $resultCount = $queryCount->result();
            $count = $resultCount[0]->count;
        }    

        $sql = "select  o.*, c.name as typegroups_name
                from $this->_name o, typegropus c 
                where o.typegroups_id = c.id
                $condition
                order by $options->orderField $options->orderDirection
                LIMIT   $options->start, $options->limit";
        $query = $this->db->query($sql);    
        if ($query->num_rows() > 0){
            $items = $query->result();
        }   
        $res = array(
            'results' => $count,
            "sql"=>$sql,
            'items' => $items
        );
        return $res;
    }
    public function getRegisteredUsers($group_id){
        $items = array();
        $count = 0;

        //obtenemos total de rtegistros sin filtros
        $count=0;
        $sqlCount = "select  count(*) as count
                    from    users u, _rols r, rols_groups rg
                    where   rg.groups_id = $group_id
                    and     rg.users_id = u.id
                    and     rg._rols_id = r.id";

        //echo $sqlCount;exit;


        $queryCount = $this->db->query($sqlCount);        
        if ($queryCount->num_rows() > 0){
            $resultCount = $queryCount->result();
            $count = $resultCount[0]->count;
        }    

        $sql = "select  concat(u.names, ' ', u.lastnames) as fullname,
                        u.email,
                        r.name as rols_name,
                        rg.dcreated,
                        rg._rols_id,
                        rg.groups_id,
                        rg.users_id
                from    users u, _rols r, rols_groups rg
                where   rg.groups_id = $group_id
                and     rg.users_id = u.id
                and     rg._rols_id = r.id
                order   by u.names ASC";

        $query = $this->db->query($sql);    
        if ($query->num_rows() > 0){
            $items = $query->result();
        }   
        $res = array(
            'results' => $count,
            "sql"=>$sql,
            'items' => $items
        );
        return $res;
    }

    public function getRegisteredObjects($group_id, $options = null){
        $items = array();
        $count = 0;
        $typeFilter = "";
        if($options!=null){
            if(!empty($options->type_objects_id))
                $typeFilter .= " and t.id = $options->type_objects_id ";
            if(!empty($options->modules_id))
                $typeFilter .= " and m.id = $options->modules_id ";
            if(!empty($options->category_id))
                $typeFilter .= " and o.category_id = $options->category_id ";
            if(!empty($options->name))
                $typeFilter .= " and o.name like '%$options->name%' ";
            if(!empty($options->description))
                $typeFilter .= " and o.description like '%$options->description%' ";
            if(!empty($options->autor))
                $typeFilter .= " and u.names like '%$options->autor%' ";
            if(!empty($options->modificator))
                $typeFilter .= " and o.umodified like '%$options->modificator%' ";

        }
        //obtenemos total de rtegistros sin filtros
        $count=0;
        $sqlCount = "select count(*) as count 
                    from    modules m, modules_objects mo, objects o, type_objects t, categories c, users u
                    where   m.groups_id = $group_id
                    and     m.id = mo.modules_id
                    and     mo.objects_id = o.id
                    and     o.type_objects_id = t.id
                    and     o.category_id = c.id
                    and     o.users_id = u.id
                    $typeFilter";

        //echo $sqlCount;exit;


        $queryCount = $this->db->query($sqlCount);        
        if ($queryCount->num_rows() > 0){
            $resultCount = $queryCount->result();
            $count = $resultCount[0]->count;
        }    

        $sql = "select  o.*, t.name as type_objects_name, 
                        c.name as category_name, 
                        mo.dcreated as dsubscribed,
                        mo.objectversion as subscribedversion,
                        mo.config,
                        mo.modules_id,
                        u.names, u.lastnames
                from    modules m, modules_objects mo, objects o, type_objects t, categories c, users u
                where   m.groups_id = $group_id
                and     m.id = mo.modules_id
                and     mo.objects_id = o.id
                and     o.type_objects_id = t.id
                and     o.category_id = c.id
                and     o.users_id = u.id
                $typeFilter
                order by t.name ASC, o.name ASC
                ";

        $query = $this->db->query($sql);    
        if ($query->num_rows() > 0){
            $items = $query->result();

            for ($i=0; $i < count($items); $i++) { 
                $items[$i]->config = str_replace("&quot;", '"', $items[$i]->config);
                $items[$i]->config = json_decode($items[$i]->config);
                $items[$i]->dstart = $items[$i]->config->start;
                $items[$i]->dend = $items[$i]->config->end;
            }

        }   
        $res = array(
            'results' => $count,
            "sql"=>$sql,
            'items' => $items
        );
        return $res;
    }
}

